-- 创建贷款还款处理存储过程（不使用序列）
CREATE OR REPLACE PROCEDURE PROCESS_LOAN_REPAYMENT(
    p_loan_id           IN  VARCHAR2,
    p_schedule_id       IN  VARCHAR2 DEFAULT NULL,
    p_repayment_date    IN  DATE DEFAULT SYSDATE,
    p_repayment_amount  IN  NUMBER,
    p_repayment_method  IN  VARCHAR2,
    p_payment_account   IN  VARCHAR2,
    p_operator_id       IN  VARCHAR2,
    p_operator_branch   IN  VARCHAR2,
    p_repayment_type    IN  VARCHAR2 DEFAULT '01',
    p_result_code       OUT NUMBER,
    p_result_msg        OUT VARCHAR2,
    p_record_id         OUT VARCHAR2
)
IS
    -- 声明变量
    v_outstanding_principal   NUMBER(15,2) := 0;
    v_outstanding_interest   NUMBER(15,2) := 0;
    v_payment_principal      NUMBER(15,2) := 0;
    v_payment_interest       NUMBER(15,2) := 0;
    v_payment_penalty        NUMBER(15,2) := 0;
    v_payment_compound       NUMBER(15,2) := 0;
    v_new_outstanding_principal NUMBER(15,2) := 0;
    v_new_outstanding_interest  NUMBER(15,2) := 0;
    v_overdue_days           NUMBER(5) := 0;
    v_due_amount             NUMBER(15,2) := 0;
    v_due_principal          NUMBER(15,2) := 0;
    v_due_interest           NUMBER(15,2) := 0;
    v_penalty_rate           NUMBER(8,6) := 0.0005; -- 日罚息率，可根据实际情况调整

    -- 贷款信息变量
    v_loan_amount            NUMBER(15,2);
    v_loan_term              NUMBER(3);
    v_loan_status            VARCHAR2(2);
    v_disbursement_date      DATE;
    v_maturity_date          DATE;
    v_disbursement_branch    VARCHAR2(10);

    -- 还款计划信息变量
    v_period_no              NUMBER(3);
    v_due_date               DATE;
    v_schedule_status        VARCHAR2(1);
    v_payment_status         VARCHAR2(1);

    -- 临时变量
    v_remaining_amount       NUMBER(15,2);
    v_transaction_id         VARCHAR2(30);
    v_voucher_no             VARCHAR2(30);
    v_max_record_id          VARCHAR2(22);
    v_next_record_id         VARCHAR2(22);
    v_max_voucher_no         VARCHAR2(30);
    v_next_voucher_no        VARCHAR2(30);

    -- 异常定义
    invalid_loan_exception      EXCEPTION;
    invalid_amount_exception    EXCEPTION;
    insufficient_amount_exception EXCEPTION;
    loan_closed_exception       EXCEPTION;

    -- 游标：获取贷款信息
    CURSOR c_loan_info IS
        SELECT LOAN_AMOUNT,
               OUTSTANDING_PRINCIPAL,
               OUTSTANDING_INTEREST,
               LOAN_STATUS,
               LOAN_TERM,
               DISBURSEMENT_DATE,
               MATURITY_DATE,
               DISBURSEMENT_BRANCH
        FROM LOAN_DISBURSEMENT
        WHERE LOAN_ID = p_loan_id
        FOR UPDATE;

    -- 游标：获取还款计划信息
    CURSOR c_schedule_info IS
        SELECT SCHEDULE_ID,
               PERIOD_NO,
               DUE_DATE,
               DUE_AMOUNT,
               DUE_PRINCIPAL,
               DUE_INTEREST,
               OUTSTANDING_PRINCIPAL,
               PAYMENT_STATUS,
               SCHEDULE_STATUS,
               OVERDUE_DAYS
        FROM REPAYMENT_SCHEDULE
        WHERE LOAN_ID = p_loan_id
          AND (SCHEDULE_ID = p_schedule_id OR p_schedule_id IS NULL)
          AND PAYMENT_STATUS IN ('1', '4')  -- 未还或部分还款
        ORDER BY PERIOD_NO
        FOR UPDATE;

BEGIN
    -- 初始化返回值
    p_result_code := 0;
    p_result_msg := '还款处理成功';
    p_record_id := NULL;

    -- 验证还款金额
    IF p_repayment_amount <= 0 THEN
        RAISE invalid_amount_exception;
    END IF;

    -- 获取贷款信息
    OPEN c_loan_info;
    FETCH c_loan_info INTO v_loan_amount, v_outstanding_principal, v_outstanding_interest,
                           v_loan_status, v_loan_term, v_disbursement_date, v_maturity_date,
                           v_disbursement_branch;

    IF c_loan_info%NOTFOUND THEN
        CLOSE c_loan_info;
        RAISE invalid_loan_exception;
    END IF;
    CLOSE c_loan_info;

    -- 检查贷款状态
    IF v_loan_status NOT IN ('02', '03') THEN  -- 只能对正常或逾期贷款还款
        RAISE loan_closed_exception;
    END IF;

    -- 生成交易流水号
    v_transaction_id := 'TXN' || TO_CHAR(p_repayment_date, 'YYYYMMDDHH24MISS') ||
                       LPAD(ABS(DBMS_RANDOM.RANDOM), 6, '0');

    -- 生成凭证号（不使用序列）
    BEGIN
        SELECT MAX(VOUCHER_NO)
        INTO v_max_voucher_no
        FROM REPAYMENT_RECORD
        WHERE VOUCHER_NO LIKE 'VCH' || TO_CHAR(p_repayment_date, 'YYYYMMDD') || '%';
        IF v_max_voucher_no IS NOT NULL THEN
            -- 从最大凭证号递增
            DECLARE
                v_seq_part VARCHAR2(8);
            BEGIN
                v_seq_part := SUBSTR(v_max_voucher_no, -8);
                v_next_voucher_no := 'VCH' || TO_CHAR(p_repayment_date, 'YYYYMMDD') ||
                                    LPAD(TO_NUMBER(v_seq_part) + 1, 8, '0');
            END;
        ELSE
            -- 从00000001开始
            v_next_voucher_no := 'VCH' || TO_CHAR(p_repayment_date, 'YYYYMMDD') || '00000001';
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_next_voucher_no := 'VCH' || TO_CHAR(p_repayment_date, 'YYYYMMDD') || '00000001';
        WHEN OTHERS THEN
            v_next_voucher_no := 'VCH' || TO_CHAR(p_repayment_date, 'YYYYMMDD') ||
                                LPAD(ABS(DBMS_RANDOM.RANDOM), 8, '0');
    END;
    v_voucher_no := v_next_voucher_no;
    -- 初始化剩余还款金额
    v_remaining_amount := p_repayment_amount;
    -- 按期次顺序处理还款
    FOR schedule_rec IN c_schedule_info LOOP
        -- 如果还款金额已分配完毕，则退出循环
        IF v_remaining_amount <= 0 THEN
            EXIT;
        END IF;
        -- 设置当前期次信息
        v_period_no := schedule_rec.PERIOD_NO;
        v_due_date := schedule_rec.DUE_DATE;
        v_due_amount := schedule_rec.DUE_AMOUNT;
        v_due_principal := schedule_rec.DUE_PRINCIPAL;
        v_due_interest := schedule_rec.DUE_INTEREST;
        v_overdue_days := schedule_rec.OVERDUE_DAYS;
        v_payment_status := schedule_rec.PAYMENT_STATUS;
        v_schedule_status := schedule_rec.SCHEDULE_STATUS;
        -- 计算罚息和复利（如果逾期）
        IF v_overdue_days > 0 THEN
            v_payment_penalty := v_due_amount * v_penalty_rate * v_overdue_days;
            -- 简化处理，实际业务中复利计算更复杂
            v_payment_compound := 0;
        ELSE
            v_payment_penalty := 0;
            v_payment_compound := 0;
        END IF;
        -- 按照先利息后本金的原则分配还款金额
        -- 1. 先偿还利息
        IF v_remaining_amount > v_due_interest THEN
            v_payment_interest := v_due_interest;
            v_remaining_amount := v_remaining_amount - v_due_interest;
        ELSE
            v_payment_interest := v_remaining_amount;
            v_remaining_amount := 0;
        END IF;
        -- 2. 再偿还本金（如果还有余额）
        IF v_remaining_amount > 0 THEN
            IF v_remaining_amount > v_due_principal THEN
                v_payment_principal := v_due_principal;
                v_remaining_amount := v_remaining_amount - v_due_principal;
            ELSE
                v_payment_principal := v_remaining_amount;
                v_remaining_amount := 0;
            END IF;
        END IF;
        -- 3. 最后处理罚息和复利（如果还有余额）
        IF v_remaining_amount > 0 THEN
            IF v_remaining_amount > v_payment_penalty THEN
                v_payment_penalty := v_payment_penalty;
                v_remaining_amount := v_remaining_amount - v_payment_penalty;
            ELSE
                v_payment_penalty := v_remaining_amount;
                v_remaining_amount := 0;
            END IF;
        END IF;
        -- 如果还有余额，处理复利
        IF v_remaining_amount > 0 THEN
            IF v_remaining_amount > v_payment_compound THEN
                v_payment_compound := v_payment_compound;
                v_remaining_amount := v_remaining_amount - v_payment_compound;
            ELSE
                v_payment_compound := v_remaining_amount;
                v_remaining_amount := 0;
            END IF;
        END IF;
        -- 计算新的待还金额
        v_new_outstanding_principal := v_due_principal - v_payment_principal;
        v_new_outstanding_interest := v_due_interest - v_payment_interest;
        -- 更新还款计划状态
        IF v_new_outstanding_principal = 0 AND v_new_outstanding_interest = 0 THEN
            -- 本期已还清
            v_payment_status := '2';  -- 正常
        ELSIF v_payment_principal > 0 OR v_payment_interest > 0 THEN
            -- 部分还款
            v_payment_status := '4';  -- 部分
        ELSIF v_overdue_days > 0 THEN
            -- 逾期未还
            v_payment_status := '3';  -- 逾期
        ELSE
            -- 未到期未还
            v_payment_status := '1';  -- 未还
        END IF;
        -- 更新还款计划表
        UPDATE REPAYMENT_SCHEDULE
        SET ACTUAL_PAYMENT_DATE = p_repayment_date,
            ACTUAL_PRINCIPAL = ACTUAL_PRINCIPAL + v_payment_principal,
            ACTUAL_INTEREST = ACTUAL_INTEREST + v_payment_interest,
            ACTUAL_AMOUNT = ACTUAL_AMOUNT + v_payment_principal + v_payment_interest,
            PENALTY_INTEREST = PENALTY_INTEREST + v_payment_penalty,
            COMPOUND_INTEREST = COMPOUND_INTEREST + v_payment_compound,
            PAYMENT_STATUS = v_payment_status,
            OVERDUE_DAYS = CASE WHEN v_payment_status IN ('2', '4') THEN 0 ELSE OVERDUE_DAYS END,
            UPDATE_DATE = SYSDATE,
            UPDATE_USER = p_operator_id
        WHERE SCHEDULE_ID = schedule_rec.SCHEDULE_ID;

    END LOOP;

    -- 计算总的贷款层面还款金额
    v_payment_principal := p_repayment_amount - v_remaining_amount;  -- 已分配的本金
    v_payment_interest := 0;  -- 在循环中已经处理了利息

    -- 更新贷款发放表中的余额
    v_new_outstanding_principal := v_outstanding_principal - v_payment_principal;
    v_new_outstanding_interest := GREATEST(v_outstanding_interest - v_payment_interest, 0);

    -- 确定新的贷款状态
    IF v_new_outstanding_principal = 0 THEN
        v_loan_status := '02';  -- 还清，状态改为正常
    ELSIF v_new_outstanding_principal < v_outstanding_principal THEN
        -- 部分还款，如果是逾期则保持逾期状态，否则为正常
        IF v_loan_status = '03' AND EXISTS (
            SELECT 1 FROM REPAYMENT_SCHEDULE
            WHERE LOAN_ID = p_loan_id AND PAYMENT_STATUS = '3'
        ) THEN
            v_loan_status := '03';  -- 仍有逾期期次
        ELSE
            v_loan_status := '02';  -- 无逾期期次
        END IF;
    END IF;

    -- 更新贷款发放表
    UPDATE LOAN_DISBURSEMENT
    SET OUTSTANDING_PRINCIPAL = v_new_outstanding_principal,
        OUTSTANDING_INTEREST = v_new_outstanding_interest,
        OVERDUE_DAYS = CASE WHEN v_new_outstanding_principal = 0 THEN 0 ELSE OVERDUE_DAYS END,
        LOAN_STATUS = CASE WHEN v_new_outstanding_principal = 0 THEN '02' ELSE LOAN_STATUS END,
        UPDATE_DATE = SYSDATE,
        UPDATE_USER = p_operator_id
    WHERE LOAN_ID = p_loan_id;

    -- 生成还款记录ID（不使用序列）
    BEGIN
        SELECT MAX(RECORD_ID)
        INTO v_max_record_id
        FROM REPAYMENT_RECORD
        WHERE RECORD_ID LIKE 'REC' || TO_CHAR(p_repayment_date, 'YYYYMMDD') || '%';

        IF v_max_record_id IS NOT NULL THEN
            -- 从最大记录ID递增
            DECLARE
                v_seq_part VARCHAR2(8);
            BEGIN
                v_seq_part := SUBSTR(v_max_record_id, -8);
                v_next_record_id := 'REC' || TO_CHAR(p_repayment_date, 'YYYYMMDD') ||
                                   LPAD(TO_NUMBER(v_seq_part) + 1, 8, '0');
            END;
        ELSE
            -- 从00000001开始
            v_next_record_id := 'REC' || TO_CHAR(p_repayment_date, 'YYYYMMDD') || '00000001';
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_next_record_id := 'REC' || TO_CHAR(p_repayment_date, 'YYYYMMDD') || '00000001';
        WHEN OTHERS THEN
            v_next_record_id := 'REC' || TO_CHAR(p_repayment_date, 'YYYYMMDD') ||
                               LPAD(ABS(DBMS_RANDOM.RANDOM), 8, '0');
    END;
    p_record_id := v_next_record_id;
    -- 插入还款记录
    INSERT INTO REPAYMENT_RECORD (
        RECORD_ID,
        LOAN_ID,
        SCHEDULE_ID,
        PAYMENT_DATE,
        PAYMENT_TYPE,
        PAYMENT_METHOD,
        PAYMENT_ACCOUNT,
        PAYMENT_PRINCIPAL,
        PAYMENT_INTEREST,
        PAYMENT_PENALTY,
        PAYMENT_COMPOUND,
        PAYMENT_AMOUNT,
        OUTSTANDING_PRINCIPAL,
        OUTSTANDING_INTEREST,
        PAYMENT_STATUS,
        VOUCHER_NO,
        OPERATOR_ID,
        OPERATOR_BRANCH,
        TRANSACTION_ID,
        CREATE_DATE,
        CREATE_USER
    ) VALUES (
        p_record_id,
        p_loan_id,
        p_schedule_id,
        p_repayment_date,
        p_repayment_type,
        p_repayment_method,
        p_payment_account,
        v_payment_principal,
        v_payment_interest,
        v_payment_penalty,
        v_payment_compound,
        p_repayment_amount,
        v_new_outstanding_principal,
        v_new_outstanding_interest,
        '1',  -- 正常
        v_voucher_no,
        p_operator_id,
        p_operator_branch,
        v_transaction_id,
        SYSDATE,
        p_operator_id
    );
    -- 记录操作日志
    INSERT INTO AUDIT_LOG (
        LOG_ID,
        TABLE_NAME,
        OPERATION_TYPE,
        NEW_VALUES,
        OPERATION_DATE,
        OPERATION_USER
    ) VALUES (
        NVL((SELECT MAX(LOG_ID) FROM AUDIT_LOG), 0) + 1,
        'REPAYMENT_RECORD',
        'INSERT',
        'LOAN_ID:' || p_loan_id || ',AMOUNT:' || p_repayment_amount || ',RECORD_ID:' || p_record_id,
        SYSDATE,
        p_operator_id
    );
    -- 提交事务
    COMMIT;
    -- 设置返回消息
    IF v_remaining_amount > 0 THEN
        p_result_msg := '还款处理成功，多余金额: ' || v_remaining_amount;
    ELSE
        p_result_msg := '还款处理成功';
    END IF;
EXCEPTION
    WHEN invalid_loan_exception THEN
        ROLLBACK;
        p_result_code := 1;
        p_result_msg := '无效的贷款ID';
    WHEN invalid_amount_exception THEN
        ROLLBACK;
        p_result_code := 2;
        p_result_msg := '还款金额必须大于0';
    WHEN loan_closed_exception THEN
        ROLLBACK;
        p_result_code := 3;
        p_result_msg := '贷款状态不允许还款';
    WHEN OTHERS THEN
        ROLLBACK;
        p_result_code := -1;
        p_result_msg := '还款处理过程中发生错误: ' || SQLERRM;
END PROCESS_LOAN_REPAYMENT;
-- 创建提前还款处理存储过程（不使用序列）
CREATE OR REPLACE PROCEDURE PROCESS_ADVANCE_REPAYMENT(
    p_loan_id           IN  VARCHAR2,
    p_repayment_date    IN  DATE DEFAULT SYSDATE,
    p_repayment_method  IN  VARCHAR2,
    p_payment_account   IN  VARCHAR2,
    p_operator_id       IN  VARCHAR2,
    p_operator_branch   IN  VARCHAR2,
    p_result_code       OUT NUMBER,
    p_result_msg        OUT VARCHAR2,
    p_record_id         OUT VARCHAR2
)
IS
    v_total_outstanding     NUMBER(15,2);
    v_advance_penalty_rate  NUMBER(8,6) := 0.01;  -- 提前还款违约金率，可根据政策调整
    v_advance_penalty       NUMBER(15,2);
    v_total_repayment       NUMBER(15,2);
BEGIN
    -- 计算总欠款
    SELECT OUTSTANDING_PRINCIPAL + OUTSTANDING_INTEREST
    INTO v_total_outstanding
    FROM LOAN_DISBURSEMENT
    WHERE LOAN_ID = p_loan_id;
    -- 计算提前还款违约金
    v_advance_penalty := v_total_outstanding * v_advance_penalty_rate;
    -- 计算总还款金额
    v_total_repayment := v_total_outstanding + v_advance_penalty;
    -- 调用普通还款过程
    PROCESS_LOAN_REPAYMENT(
        p_loan_id => p_loan_id,
        p_repayment_date => p_repayment_date,
        p_repayment_amount => v_total_repayment,
        p_repayment_method => p_repayment_method,
        p_payment_account => p_payment_account,
        p_operator_id => p_operator_id,
        p_operator_branch => p_operator_branch,
        p_repayment_type => '02',  -- 提前还款
        p_result_code => p_result_code,
        p_result_msg => p_result_msg,
        p_record_id => p_record_id
    );

    -- 如果成功，在备注中添加提前还款标识
    IF p_result_code = 0 THEN
        UPDATE REPAYMENT_RECORD
        SET REMARK = '提前还款，违约金: ' || v_advance_penalty
        WHERE RECORD_ID = p_record_id;

        COMMIT;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_result_code := 1;
        p_result_msg := '找不到指定的贷款记录';
    WHEN OTHERS THEN
        p_result_code := -1;
        p_result_msg := '提前还款处理错误: ' || SQLERRM;

END PROCESS_ADVANCE_REPAYMENT;

-- 创建计算和更新贷款逾期天数的存储过程
CREATE OR REPLACE PROCEDURE CALCULATE_OVERDUE_DAYS(
    p_loan_id       IN  VARCHAR2 DEFAULT NULL,
    p_process_date  IN  DATE DEFAULT SYSDATE,
    p_result_code   OUT NUMBER,
    p_result_msg    OUT VARCHAR2
)
IS
    -- 游标定义：获取需要处理的贷款信息
    CURSOR c_loans_to_process IS
        SELECT ld.LOAN_ID,
               ld.OUTSTANDING_PRINCIPAL,
               ld.OUTSTANDING_INTEREST,
               ld.OVERDUE_DAYS,
               ld.OVERDUE_AMOUNT,
               rs.DUE_DATE,
               rs.DUE_AMOUNT,
               rs.DUE_PRINCIPAL,
               rs.DUE_INTEREST
        FROM LOAN_DISBURSEMENT ld
        JOIN REPAYMENT_SCHEDULE rs ON ld.LOAN_ID = rs.LOAN_ID
        WHERE ld.LOAN_STATUS IN ('02', '03') -- 正常或逾期状态
          AND rs.PAYMENT_STATUS IN ('1', '4') -- 未还或部分还款
          AND rs.DUE_DATE < p_process_date
          AND (p_loan_id IS NULL OR ld.LOAN_ID = p_loan_id);
    -- 变量声明
    v_new_overdue_days    NUMBER(5) := 0;
    v_new_overdue_amount  NUMBER(15,2) := 0;
    v_loan_status         VARCHAR2(2);
    v_updated_count       NUMBER := 0;
BEGIN
    -- 初始化返回值
    p_result_code := 0;
    p_result_msg := '处理成功';
    -- 处理每笔贷款
    FOR rec IN c_loans_to_process LOOP
        -- 计算逾期天数
        v_new_overdue_days := p_process_date - rec.DUE_DATE;
        v_new_overdue_amount := rec.DUE_AMOUNT;
        -- 根据逾期天数确定贷款状态
        IF v_new_overdue_days > 0 AND v_new_overdue_days <= 30 THEN
            v_loan_status := '03'; -- 逾期
        ELSIF v_new_overdue_days > 30 AND v_new_overdue_days <= 90 THEN
            v_loan_status := '04'; -- 呆滞
        ELSIF v_new_overdue_days > 90 THEN
            v_loan_status := '05'; -- 呆账
        ELSE
            v_loan_status := '02'; -- 正常
        END IF;
        -- 更新还款计划表中的逾期信息
        UPDATE REPAYMENT_SCHEDULE
        SET OVERDUE_DAYS = v_new_overdue_days,
            PAYMENT_STATUS = CASE
                               WHEN v_new_overdue_days > 0 THEN '3' -- 逾期
                               ELSE PAYMENT_STATUS
                             END,
            UPDATE_DATE = SYSDATE,
            UPDATE_USER = USER
        WHERE LOAN_ID = rec.LOAN_ID
          AND DUE_DATE = rec.DUE_DATE;
        -- 更新贷款发放表中的逾期信息
        UPDATE LOAN_DISBURSEMENT
        SET OVERDUE_DAYS = v_new_overdue_days,
            OVERDUE_AMOUNT = v_new_overdue_amount,
            LOAN_STATUS = v_loan_status,
            LOAN_CLASSIFICATION = CASE
                                    WHEN v_new_overdue_days > 90 THEN '5'  -- 损失
                                    WHEN v_new_overdue_days > 30 THEN '4'  -- 可疑
                                    WHEN v_new_overdue_days > 10 THEN '3'  -- 次级
                                    WHEN v_new_overdue_days > 0 THEN '2'   -- 关注
                                    ELSE LOAN_CLASSIFICATION
                                  END,
            UPDATE_DATE = SYSDATE,
            UPDATE_USER = USER
        WHERE LOAN_ID = rec.LOAN_ID;
        v_updated_count := v_updated_count + 1;
    END LOOP;
    -- 提交事务
    COMMIT;
    p_result_msg := '成功处理 ' || v_updated_count || ' 笔贷款的逾期状态';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        p_result_code := -1;
        p_result_msg := '处理过程中发生错误: ' || SQLERRM;
END CALCULATE_OVERDUE_DAYS;

-- 创建贷款风险等级划分和催收处理存储过程
CREATE OR REPLACE PROCEDURE LOAN_COLLECTION_PROCESS(
    p_branch_code     IN  VARCHAR2 DEFAULT NULL,
    p_min_overdue_days IN NUMBER DEFAULT 1,
    p_process_date    IN  DATE DEFAULT SYSDATE,
    p_result_code     OUT NUMBER,
    p_result_msg      OUT VARCHAR2
)
IS
    -- 定义游标：获取需要进行催收处理的逾期贷款
    CURSOR c_overdue_loans IS
        SELECT ld.LOAN_ID,
               ld.CUSTOMER_ID,
               ld.LOAN_AMOUNT,
               ld.OUTSTANDING_PRINCIPAL,
               ld.OUTSTANDING_INTEREST,
               ld.OVERDUE_DAYS,
               ld.OVERDUE_AMOUNT,
               ld.LOAN_CLASSIFICATION,
               ld.DISBURSEMENT_BRANCH,
               ld.CUSTOMER_ID,
               cp.PRODUCT_NAME,
               cp.PRODUCT_TYPE
        FROM LOAN_DISBURSEMENT ld
        JOIN CREDIT_PRODUCT cp ON ld.PRODUCT_ID = cp.PRODUCT_ID
        WHERE ld.LOAN_STATUS IN ('03', '04', '05')  -- 逾期、呆滞、呆账状态
          AND ld.OVERDUE_DAYS >= p_min_overdue_days
          AND (p_branch_code IS NULL OR ld.DISBURSEMENT_BRANCH = p_branch_code)
        ORDER BY ld.OVERDUE_DAYS DESC;
    -- 催收等级变量
    v_collection_level  VARCHAR2(2);
    v_risk_category     VARCHAR2(1);
    v_collection_action VARCHAR2(200);
    v_updated_count     NUMBER := 0;
    v_error_count       NUMBER := 0;
BEGIN
    -- 初始化返回值
    p_result_code := 0;
    p_result_msg := '催收处理完成';
    -- 处理每一笔逾期贷款
    FOR loan_rec IN c_overdue_loans LOOP
        BEGIN
            -- 根据逾期天数划分催收等级
            IF loan_rec.OVERDUE_DAYS BETWEEN 1 AND 30 THEN
                v_collection_level := '01';  -- 初级催收
                v_risk_category := '2';     -- 关注类
                v_collection_action := '电话提醒';
            ELSIF loan_rec.OVERDUE_DAYS BETWEEN 31 AND 90 THEN
                v_collection_level := '02';  -- 中级催收
                v_risk_category := '3';     -- 次级类
                v_collection_action := '上门催收';
            ELSIF loan_rec.OVERDUE_DAYS BETWEEN 91 AND 180 THEN
                v_collection_level := '03';  -- 高级催收
                v_risk_category := '4';     -- 可疑类
                v_collection_action := '律师函';
            ELSIF loan_rec.OVERDUE_DAYS > 180 THEN
                v_collection_level := '04';  -- 法律催收
                v_risk_category := '5';     -- 损失类
                v_collection_action := '法律诉讼';
            ELSE
                v_collection_level := '00';  -- 无需催收
                v_risk_category := '1';     -- 正常类
                v_collection_action := '无';
            END IF;
            -- 更新贷款分类（如果需要）
            UPDATE LOAN_DISBURSEMENT
            SET LOAN_CLASSIFICATION = v_risk_category,
                UPDATE_DATE = p_process_date,
                UPDATE_USER = USER
            WHERE LOAN_ID = loan_rec.LOAN_ID
              AND LOAN_CLASSIFICATION != v_risk_category;
            -- 记录催收操作到审计日志
            INSERT INTO AUDIT_LOG (
                LOG_ID,
                TABLE_NAME,
                OPERATION_TYPE,
                NEW_VALUES,
                OPERATION_DATE,
                OPERATION_USER
            ) VALUES (
                SEQ_AUDIT_LOG.NEXTVAL,
                'LOAN_COLLECTION',
                'COLLECTION',
                'LOAN_ID:' || loan_rec.LOAN_ID ||
                ',LEVEL:' || v_collection_level ||
                ',ACTION:' || v_collection_action ||
                ',DAYS:' || loan_rec.OVERDUE_DAYS,
                p_process_date,
                USER
            );
            v_updated_count := v_updated_count + 1;
        EXCEPTION
            WHEN OTHERS THEN
                v_error_count := v_error_count + 1;
                -- 记录错误到审计日志
                INSERT INTO AUDIT_LOG (
                    LOG_ID,
                    TABLE_NAME,
                    OPERATION_TYPE,
                    NEW_VALUES,
                    OPERATION_DATE,
                    OPERATION_USER
                ) VALUES (
                    SEQ_AUDIT_LOG.NEXTVAL,
                    'LOAN_COLLECTION_ERROR',
                    'ERROR',
                    'LOAN_ID:' || loan_rec.LOAN_ID || ',ERROR:' || SQLERRM,
                    p_process_date,
                    USER
                );
        END;
    END LOOP;
    COMMIT;
    p_result_msg := '催收处理完成: 成功处理 ' || v_updated_count || ' 笔贷款，错误 ' || v_error_count || ' 笔';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        p_result_code := -1;
        p_result_msg := '催收处理过程中发生错误: ' || SQLERRM;
END LOAN_COLLECTION_PROCESS;
